package org.biojava.services.das.dao;

import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.sql.DataSource;

import org.biojava.dasobert.dasregistry.Das1Validator;
import org.biojava.dasobert.dasregistry.DasSource;
import org.biojava.services.das.registry.ConfigBean;
import org.biojava.services.das.registry.DasRegistry;
import org.biojava.services.das.registry.DasRegistrySql;
import org.biojava.services.das.registry.PassKeyGenerator;
import org.biojava.services.das.registry.UserManager;
import org.biojava.services.das2.Das2Mirror;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

/**
 * Handle getting info from the database about servers (aka mirrored servers)
 * rather than individual sources
 * 
 * @author jw12
 * 
 */
public class DasServerDAO extends NamedParameterJdbcDaoSupport {

	DasUserDAO dasUserDao;
	DasSourceManager dasSourceManager;

	

	public DasSourceManager getDasSourceManager() {
		return dasSourceManager;
	}

	public void setDasSourceManager(DasSourceManager dasSourceManager) {
		this.dasSourceManager = dasSourceManager;
	}

	public DasUserDAO getDasUserDao() {
		return dasUserDao;
	}

	public void setDasUserDao(DasUserDAO dasUserDao) {
		this.dasUserDao = dasUserDao;
	}

	public String storeServerInDb(String serverUrl, String openId,
			String description) {
		//if result is "" then it is ok and continue
		//if anything else then we have a problem
		
		//we don't want to register it if someone owns it already i.e. user is !=0
		//if no user allocated then fine validate the sources document and if valid add the server with the owner
		String errors = "";
			if (serverUrl.endsWith("sources")) {
				return "Server url should not have sources command at the end of it! So not storing this url in database as something wrong!";
			}
			int passkey = PassKeyGenerator.generatePasskey();

			RegistryUser user = null;
			if(openId!=null){
				user = dasUserDao.getUser(openId);
			}
			

			int user_id = 0;
			if (user != null){
				user_id = user.getUserID();
			}
				
			// need to set up a servers table with this info in it
			// auto increment to get a unique id for the server
			String sql = "INSERT INTO server " + "(url," + "user_id,"
					+ "description," + "passkey" + ")";
			String endsql = " values (:url,:user_id,:description,:passkey)";
			sql += endsql;
			System.out.println("serverUrl:" + serverUrl);
			Map ps = new HashMap();
			ps.put("url", serverUrl);
			ps.put("user_id", user_id);
			ps.put("description", description);
			ps.put("passkey", passkey);
			Iterator keys = ps.keySet().iterator();
			int value = getNamedParameterJdbcTemplate().update(sql, ps);
			errors = String.valueOf(value);
		
		return errors;
	}

	public DasServer getServerForServerUrl(String  url) {
		List <DasServer> dasServers=this.getDasServers();
		for(DasServer server:dasServers){
			if(url.equals(server.getUrl())){
				return server;
			}
			
		}
		return null;
	}

	public int getUserIdForServerUrl(String url) {
		String sqlMirrors = "select user_id from server where url=:url";
		SqlParameterSource namedParameters = new MapSqlParameterSource("url",
				url.toString());
		int value = 0;
		try {
			value = getNamedParameterJdbcTemplate().queryForInt(sqlMirrors,
					namedParameters);
		} catch (DataAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("value=" + value);
		return value;

	}

	/**
	 * get unique servers from the registry table
	 * 
	 * @param conn
	 * @return
	 */
	public Set<String> getUniqueServersFromSources(Connection conn) {
		List<DasSource> sources = null;
		try {
			sources = dasSourceManager.getAllSources(conn, null);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Set<String> rootUrls = new HashSet<String>();
		for (DasSource source : sources) {
			String url = source.getUrl();
			// System.out.println("url="+url);
			String rooturl = Das1Validator.removeDataSourceNameFromUrl(url);
			// System.out.println(rooturl);
			rootUrls.add(rooturl);
		}
		return rootUrls;

	}

	public List<DasServer> getDasServers() {
		SqlParameterSource namedParameters = new MapSqlParameterSource("id", "");
		String sqlServers = "select server_id, url, user_id, description, number_of_sources from server order by number_of_sources desc";
		return getNamedParameterJdbcTemplate().query(sqlServers,
				namedParameters, new ParameterizedRowMapper<DasServer>() {
					public DasServer mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						DasServer server = new DasServer();
						server.setServerId(rs.getInt("server_id"));
						server.setUrl(rs.getString("url"));
						server.setDescription(rs.getString("description"));
						server.setNumberOfSources(rs
								.getInt("number_of_sources"));
						server.setUserId(rs.getInt("user_id"));
						return server;
					}
				});

	}

	/**
	 * if there are new servers in the list add them to the database server
	 * table
	 * 
	 * @param conn
	 * @param serverRootUrls
	 */
	public void populateServerTable(Set<String> serverRootUrls) {
		// need to get server urls first from the database and if some new ones
		// add them
		List<DasServer> mirrors = this.getDasServers();
		Set<String> knownRoots = new HashSet<String>();
		// get a list of known server urls from registry server table
		for (DasServer mirror : mirrors) {
			knownRoots.add(mirror.getUrl());
			// System.out.println("adding known|"+mirror.getUrl()+"|");
		}
		// if the registry server table does not contain the url add it to the
		// server table
		for (String rootUrl : serverRootUrls) {
			if (!knownRoots.contains(rootUrl)) {
				String message = this.storeServerInDb(rootUrl, null, null);
			}
		}

	}

	

	public String updateServer(String serverUrl, String openId, String description) {
		System.out.println("updating server");
		RegistryUser user = dasUserDao.getUser(openId);
		// update server set user_id=107 where
		// url='http://das.sbc.su.se:9000/das/';
		String sql = "update server set" + " user_id=:user_id, description=:description";
		String endsql = " where url=:url";
		sql += endsql;
		Map namedParameters = new HashMap();
		namedParameters.put("user_id", user.getUserID());
		namedParameters.put("url", serverUrl);
		namedParameters.put("description", description);
		int result = getNamedParameterJdbcTemplate().update(sql,
				namedParameters);
		if(result==1){//it's done ok
			return "";
		}else{
			return "server not updated";
		}
	}

	public String updateSourceNumbers(String sourcesUrl,
			int numberOfSourcesForServer) {
		String sql = "update server set"
				+ " number_of_sources=:number_of_sources";
		String endsql = " where url=:url";
		sql += endsql;
		Map namedParameters = new HashMap();
		namedParameters.put("number_of_sources", numberOfSourcesForServer);
		namedParameters.put("url", sourcesUrl);
		int result = getNamedParameterJdbcTemplate().update(sql,
				namedParameters);
		return "";
	}

	public String removeServer(String serverRootUrl) {
		String sql = "delete from server where url=:url";
		Map namedParameters = new HashMap();
		namedParameters.put("url", serverRootUrl);
		int result = getNamedParameterJdbcTemplate().update(sql,
				namedParameters);
		return String.valueOf(result);
	}

	public boolean exists(String serverUrl) {
		String sql = "select count(*) from server where url=:url";
		Map paramMap = new HashMap();
		paramMap.put("url", serverUrl);
		int count = 0;
		try {
			count = getNamedParameterJdbcTemplate().queryForInt(sql, paramMap);
		} catch (DataAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (count > 0) {
			return true;
		} else {
			return false;
		}
	}

}
